﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Autofac;
using Dapper;
using System.Data.SqlClient;
using LiteExpress.Core.Config;
using Microsoft.Framework.ConfigurationModel;
//using MySql.Data.MySqlClient;
//using System.Data.OracleClient;
//using System.Data.OleDb;

namespace LiteExpress.Core.DbClient
{
    public static class DbFactory
    {
        #region members
        /// <summary>
        /// 存储数据库配置节的词典（数据库级别）
        /// </summary>
        public static IDictionary<string, DataBase> DBMap;

        /// <summary>
        /// 存储Sql语句的词典（具体的查询语句级别）
        /// </summary>
        public static IDictionary<string, ObjectSql> SqlMap { get; set; }

        /// <summary>
        /// 默认的SQL文件目录 "SQLMap"
        /// </summary>
        public static readonly string SQLMappingFilePath = "sqlmap";

        /// <summary>
        /// 默认的SQL文件格式 "*.sql.xml"
        /// </summary>
        public static readonly string SQLMappingFilePattern = "*.sql.json";

        private static object loadDBObject = new object();
        private static object loadSqlMapObject = new object();

        /// <summary>
        /// 连接字符串设置，需要在初始化时从connection.config.json导入
        /// </summary>
        private static Dictionary<string, IConfiguration> ConnectionStrings { get; set; }

        #endregion

        #region method
        public static T GetConnection<T>(string dbName, string sqlName, Func<IDbConnection, string, T> func)
        {
            var sql = GetObjectSql(dbName, sqlName);
            var db = GetObjectDB(dbName);
            var conn = ResolveConnection(db);

            var result = func(conn, sql.sql);
            return result;
        }

        public static T GetConnection<T>(string dbName, Func<IDbConnection, T> func)
        {
            var db = GetObjectDB(dbName);
            var conn = ResolveConnection(db);

            var result = func(conn);
            return result;
        }

        public static void GetConnection(string dbName, string sqlName, Action<IDbConnection, string> func)
        {
            var sql = GetObjectSql(dbName, sqlName);
            var db = GetObjectDB(dbName);
            var conn = ResolveConnection(db);
            func(conn, sql.sql);
        }

        public static void GetConnection(string dbName, Action<IDbConnection> func)
        {
            var db = GetObjectDB(dbName);
            var conn = ResolveConnection(db);
            func(conn);
        }

        /// <summary>
        /// 获取数据库连接并创建事务，注意在使用dapper扩展方法的时候需要传入事务变量
        /// </summary>
        /// <example>
        /// DbFactory.GetTransactionConnection(DBName, (conn, trans) =>
        /// {
        ///     string sql1 = "UPDATE Users SET NickName=@NickName WHERE UserID=@UserID";
        ///     string sql2 = "INSERT Users (UserID,UserName) VALUES (@UserID,@UserName)";
        ///     conn.Execute(sql1, new { UserID = 666, NickName = "tom" }, trans);//如果ado.net异常，外部会自动算作返回false，并执行rollback
        ///     conn.Execute(sql2, new { UserID = 333, UserName = "jerry"}, trans);
        ///     return true;
        /// });
        /// </example>
        public static bool GetTransaction(string dbName, Func<IDbTransaction, bool> func)
        {
            var db = GetObjectDB(dbName); ;
            var conn = ResolveConnection(db);            
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }

            bool success = false;
            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    success = func(trans);
                }
                catch (DataException)
                {
                    success = false;
                }

                if (success)
                {
                    trans.Commit();
                }
                else {
                    trans.Rollback();
                }
            }
            return success;
        }        
        #endregion

        #region get sql and connection
        /// <summary>
        /// 获取真实db连接，使用autofac容器
        /// </summary>
        private static IDbConnection ResolveConnection(DataBase db)
        {
            return ContainerManager.Container.ResolveNamed<IDbConnection>("cloudconnection_" + db.DBName.ToLower());
            //return CreateConnection(db); 
        }

        /// <summary>
        /// 创建数据库连接（使用provider，需要在web.config中添加相应配置）
        /// </summary>
        private static IDbConnection CreateConnection(DataBase db)
        {
            //if (string.IsNullOrEmpty(db.ConnectionString)) throw new DbClientException("连接字符串不能为空");
            
            switch (db.DBType)
            {
                case DataBaseType.MsSql:
                    return new SqlConnection(db.ConnectionString);
                case DataBaseType.MySql:
                    throw new NotImplementedException();
                    //return new MySqlConnection(db.ConnectionString);
                case DataBaseType.Oracle:
                    throw new NotImplementedException();
                case DataBaseType.Access:
                    throw new NotImplementedException();
                default:
                    throw new NotImplementedException("暂不支持此数据库类型！");
            }

            //DbProviderFactory fac = DbProviderFactories.GetFactory(db.ProviderName);
            //var conn = fac.CreateConnection();
            //conn.ConnectionString = db.ConnectionString;
            //return conn;
        }

        private static Type GetConnectionType(DataBase db)
        {
            Type connType;
            switch (db.DBType)
            {
                case DataBaseType.MySql:
                    //connType = typeof(MySqlConnection);
                    throw new NotImplementedException();
                    break;
                case DataBaseType.Oracle:
                    //connType = typeof(OracleConnection);
                    throw new NotImplementedException();
                    break;
                case DataBaseType.Access:
                    //connType = typeof(OleDbConnection);
                    throw new NotImplementedException();
                    break;
                case DataBaseType.MsSql:
                    connType = typeof(SqlConnection);
                    break;
                default:
                    connType = typeof(SqlConnection);
                    break;
            }
            return connType;
        }

        /// <summary>
        /// 获取Sql的定义
        /// </summary>
        public static ObjectSql GetObjectSql(string dbName, string sqlName)
        {
            var db = GetObjectDB(dbName);
            var sqlKey = string.Format("{0}.{1}.{2}", dbName, sqlName, db.DBType.ToString()).ToLower();
            if (SqlMap.ContainsKey(sqlKey) == false)
            {
                if (SqlMap.ContainsKey(sqlKey) == false)
                {
                    throw new ArgumentOutOfRangeException("sqlName", sqlName, "没有定义sqlName");
                }
            }

            var oSql = SqlMap[sqlKey];
            return oSql;
        }

        /// <summary>
        /// 获取数据库定义
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static DataBase GetObjectDB(string dbName)
        {
            var dbKey = dbName.ToLower();
            if (DBMap.ContainsKey(dbKey) == false)
            {
                throw new ArgumentOutOfRangeException("dbName", dbName, string.Format("没有定义数据库{0}", dbName));
            }

            var db = DBMap[dbKey];
            return db;            
        }
        #endregion

        #region initialize
        static DbFactory()
        {
            //TODO:connection.config.json导入AppConnections
            var configuration = new Configuration()
               .AddJsonFile("config.json");

            ConnectionStrings = configuration.GetSubKeys("Data").ToDictionary(p=>p.Key, p=>p.Value);

            LoadDatabases();
            LoadSqlModules();
        }

        /// <summary>
        /// 加载配置节中的数据节点
        /// 注意：connectionstring配置节配置的数据库使用跟主库一样的数据库类型
        /// </summary>
        /// <param name="dic"></param>
        private static void GetDBFromConnectionConfig(ref Dictionary<string, DataBase> dic)
        {
            if (dic == null)
            {
                dic = new Dictionary<string, DataBase>();
            }

            //加载配置文件中的节点
            foreach (string dbName in ConnectionStrings.Keys)
            {
                var config = ConnectionStrings[dbName];

                DataBaseType dbType = DataBaseType.MsSql;
                if(!string.IsNullOrWhiteSpace(config.Get("Provider")))
                {
                    switch(config.Get("Provider"))
                    {
                        case "System.Data.SqlClient":
                            dbType = DataBaseType.MsSql;
                            break;
                        case "MySql.Data.MySqlClient":
                            dbType = DataBaseType.MySql;
                            break;
                        case "System.Data.OracleClient":
                            dbType = DataBaseType.Oracle;
                            break;
                    }
                }

                var db = new DataBase
                {
                    DBName = dbName,
                    ConnectionString = config.Get("ConnectionString"),
                    DBType = dbType,//connectionstring配置节使用跟主库一样的数据库类型
                    ProviderName = config.Get("Provider")
                };

                if (dic.ContainsKey(dbName.ToLower()))
                {
                    dic[dbName.ToLower()] = db;
                }
                else
                {
                    dic.Add(db.DBName.ToLower(), db);
                }
            }
        }
        
        /// <summary>
        /// 加载数据库节点(外部入口)
        /// </summary>
        internal static void LoadDatabases()
        {
            Dictionary<string, DataBase> dic = new Dictionary<string, DataBase>();

            GetDBFromConnectionConfig(ref dic);            

            lock (loadDBObject)
            {
                DBMap = dic;
            }

            #region 注册所有涉及到的数据库连接            
            ContainerManager.UpdateContainer(builder =>
            {
                foreach (string item in DBMap.Keys)
                {
                    Type connType = GetConnectionType(DBMap[item]);

                    builder.RegisterType(connType)
                        .WithParameter("connectionString", DBMap[item].ConnectionString)//输入构造函数参数
                        //.InstancePerLifetimeScope()//每次请求一个连接实例，由于dapper默认使用datareader，所以此种模式下会导致并发下reader未关闭占用连接的情况。
                        //.ExternallyOwned()
                        .Named<IDbConnection>("cloudconnection_" + item);//命名数据库连接实例
                }            
            });            
            #endregion
        }

        /// <summary>
        /// 加载SqlMap到内存中
        /// </summary>
        internal static void LoadSqlModules()
        {
            if (SqlMap == null)
            {
                lock (loadSqlMapObject)
                {
                    if (SqlMap == null)
                    {
                        SqlMap = new Dictionary<string, ObjectSql>(100);
                    }
                }
            }

            var filePathInfo = SQLMappingFilePath.Split(new char[] { ',' });
            var filePatternInfo = SQLMappingFilePattern.Split(new char[] { ',' });
            foreach (var path in filePathInfo)
            {
                foreach (var pattern in filePatternInfo)
                {
                    LoadSqlModules(path, pattern);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="filePattern"></param>
        private static void LoadSqlModules(string filePath, string filePattern)
        {
            Dictionary<string, ObjectSql> sqlMap = new Dictionary<string, ObjectSql>(100);

            var basePath = AppDomain.CurrentDomain.BaseDirectory;
            string sqlmapDir = string.Format("{0}{1}", basePath, filePath);
            if (!Directory.Exists(sqlmapDir))
            {
                return;
            }

            var sqlFiles = Directory.GetFiles(sqlmapDir, SQLMappingFilePattern, SearchOption.AllDirectories);
            foreach (var sqlFile in sqlFiles)
            {
                string jsonstr = string.Empty;

                var aa = new FileStream("", FileMode.Open);
                using (StreamReader sr = new System.IO.StreamReader(sqlFile))
                {
                    jsonstr = sr.ReadToEnd();
                }
                var aaa = Newtonsoft.Json.JsonConvert.DeserializeObject<SqlMapScheme>(jsonstr);

                //foreach (XmlNode moduleNode in moduleNodes)
                //{
                //    #region Module
                //    var moduleName = moduleNode.Attributes["moduleName"].InnerText;
                //    var dbName = moduleNode.Attributes["dbName"].InnerText;                    

                //    foreach (XmlNode element in sqlMapElementes)
                //    {
                //        #region ObjectSql
                        

                //        objectSql.SQLName = string.Format("{0}.{1}.{2}.{3}"
                //            , dbName, moduleName, element.Attributes["sqlName"].InnerText, ((DataBaseType)objectSql.SupportDBType).ToString().ToLower());
                //        sqlMap[objectSql.SQLName.ToLower()] = objectSql;

                //        #endregion
                //    }
                //    #endregion
                //}
            }
            lock (loadSqlMapObject)
            {
                SqlMap = sqlMap;
            }
        }

        #endregion

    }
}
